﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Shop_Inventory_Management
{
    class Constant
    {
        public const string DEFAULT_PASSWORD = "D1qU/ibus6/wqtrUj1cWpA==";
        public const string SHOP_SERVER_ID = "0010";
        public const string HQ_SERVER = "HQ";
        public const string SHOP_SERVER = "Shop";

        public const string THREAD_START = " Thread Start";
        public const string THREAD_END = " Thread End";

        public const string EMAIL_BODY_SYN = "There is an error occured durring the synchronization and update, please check you system\n";
        public const string EMAIL_BODY_UPDATE = "There is an error occured durring the update of price, please check you system\n";

        /************************Email************************************/
        public const string EMAIL_HOST = "smtp.live.com";
        public const int EMAIL_PORT = 587;
        public const string EMAIL_SENDER = "d/PHYYXwlPfUjUXVdA6KQFwLvvJR/rcaRbmbd6e3LJc=";
        public const string EMAIL_SENDER_PASSWORD = "PhXh5sb7Aixl2tSMHfeKfQ==";
        public const string EMAIL_RECEIVER = "a0087646@nus.edu.sg";

        public const string EMAIL_SUBJECT = "Shop Server Failure";
        /************************End Email********************************/

        /*****************All About Manufacturer Table********************/
        public const string MANUFACTURER_TABLE = "manufacturer";
        public const int MANUFACTURER_PRIMARYKEY_NUMBER = 1;
        //Columns
        public const string MANUFACTURER_NAME = "ManufacturerName";
        public const string MANUFACTURER_COUNTRY = "Country";
        public const string MANUFACTURER_ADDRESS = "Address";
        public const string MANUFACTURER_CONTACT = "Contact";
        /******************End Manufacturer Table**************************/


        /******************All About Product Table************************/
        public const string PRODUCT_TABLE = "product";
        public const int PRODUCT_PRIMARYKEY_NUMBER = 1;
        //Columns
        public const string PRODUCT_MANUFACTURER_NAME = "ManufacturerName";
        public const string PRODUCT_ID = "ProductID";
        public const string PRODUCT_NAME = "Name";
        public const string PRODUCT_CATEGORY = "Category";
        public const string PRODUCT_MINIMUM_STOCK = "MinimumStock";
        public const string PRODUCT_MAXIMUM_STOCK = "MaximumStock";
        /*****************End Product Table********************************/


        /******************All About Stock Table************************/
        public const string STOCK_TABLE = "stock";
        public const int STOCK_PRIMARYKEY_NUMBER = 2;
        //Columns
        public const string STOCK_PRODUCT_ID = "ProductID";
        public const string STOCK_BATCH_ID = "BatchID";
        public const string STOCK_IMPORT_PRICE = "ImportPrice";
        public const string STOCK_SELLING_PRICE = "SellingPrice";
        public const string STOCK_EXPIRE_DATE = "ExpireDate";
        public const string STOCK_QUANTITY = "Quantity";
        public const string STOCK_DEFAULT_DISCOUNT_POLICY = "DefaultDiscountPolicy";
        /*****************End Stock Table********************************/


        /*****************All About Discount Table************************/
        public const string DISCOUNT_TABLE = "discount";
        public const int DISCOUNT_PRIMARYKEY_NUMBER = 1;
        //Columns
        public const string DISCOUNT_PRODUCT_ID = "ProductID";
        public const string DISCOUNT_EFFECTIVE_DATE = "EffectiveDate";
        public const string DISCOUNT_BUNDLEUNIT = "BundleUnit";
        public const string DISCOUNT_FREE_ITEM_QUANTITY = "FreeItemQuantity";
        public const string DISCOUNT_DISCOUNT = "Discount";
        /*****************End Discount Table*******************************/


        /******************All About Request Table************************/
        public const string REQUEST_TABLE = "request";
        public const int QH_REQUEST_PRIMARYKEY_NUMBER = 3;
        public const int SHOP_REQUEST_PRIMARYKEY_NUMBER = 2;
        //Columns
        public const string RE_PRODUCT_ID = "ProductID";
        public const string RE_SHOP_ID = "ShopID";
        public const string RE_REQUEST_DATE = "RequestDate";
        public const string RE_QUANTITY = "Quantity";
        public const string RE_URGENCY = "Urgency";
        /*****************End Request Table********************************/


        /******************All About Report Table************************/
        public const string REPORT_TABLE = "report";
        public const int REPORT_PRIMARYKEY_NUMBER = 2;
        //Columns
        public const string REPORT_SHOP_ID = "ShopID";
        public const string REPORT_SHOP_PASSWORD = "Password";
        public const string REPORT_DATE = "ReportDate";
        public const string REPORT_TOTAL_COST_PRICE = "TotalCostPrice";
        public const string REPORT_TOTAL_SELLING_PRICE = "TotalSellingPrice";
        public const string REPORT_SELLING_QUANTITY = "SellingQuantity";
        /*****************End Report Table********************************/


        /******************All About Transactions Table************************/
        //public const string TRANSACTIONS_TABLE = "Transactions";
        ////Columns
        //public const string TRANSACTION_ID = "TransactionID";
        //public const string CASHIER_ID = "CashierID";
        //public const string MACHINE_ID = "MachineID";
        //public const string TRANSACTION_DATE = "TransactionDate";
        /*****************End Transactions Table********************************/


        /******************All About CheckOutItem Table************************/
        //public const string CHECKOUTITEM_TABLE = "Check";
        ////Columns
        //public const string CHECKOUTITEM_TRANSACTION_ID = "TransactionID";
        //public const string CHECKOUTITEM_PRODUCT_ID = "ProductID";
        //public const string CHECKOUTITEM_BATCH_ID = "BatchID";
        //public const string CHECKOUTITEM_COST_PRICE = "CostPrice";
        //public const string CHECKOUTITEM_SELLING_PRICE = "SellingPrice";
        //public const string CHECKOUTITEM_DISCOUNT = "Discount";
        //public const string CHECKOUTITEM_QUANTITY = "Quantity";
        /*****************End Transactions Table********************************/


        /******************All About RequestApproveStatus Table************************/
        public const string REQUEST_APPROVE_STATUS_TABLE = "requestapprovestatus";
        public const int HQ_REQUEST_APPROVE_STATUS_PRIMARYKEY_NUMBER = 4;
        public const int SHOP_REQUEST_APPROVE_STATUS_PRIMARYKEY_NUMBER = 2;
        public const string PENDING_REQUEST_TABLE = "pendingRequest";
        public const string APPROVED_REQUEST_TABLE = "approvedRequest";
        public const string REJECTED_REQUEST_TABLE = "rejectedRequest";
        //Columns
        public const string RE_AP_PRODUCT_ID = "ProductID";
        public const string RE_AP_SHOP_ID = "ShopID";
        public const string RE_AP_REQUEST_DATE = "RequestDate";
        public const string RE_AP_STAFF_ID = "StaffID";
        public const string RE_AP_APPROVED = "Approved";
        public const string RE_AP_QUANTITY = "ApprovedQuantity";
        public const string RE_AP_ACTION_DATE = "ActionDate";        
        public const string RE_AP_COMMENTS = "Comments";
        /*****************End RequestApproveStatus Table********************************/


        /******************All About Shop Table************************/
        public const string SHOP_TABLE = "shop";
        //Columns
        public const string SHOP_ID = "ShopID";
        public const string SHOP_COUNTRY = "Country";
        public const string SHOP_LOCATION = "Location";
        public const string SHOP_CONTACT = "Contact";
        /*****************End Shop Table********************************/


        /******************All About Staff Table************************/
        public const string STAFF_TABLE = "staff";
        //Columns
        public const string STAFF_ID = "StaffID";
        public const string STAFF_NAME = "Name";
        public const string STAFF_PASSWORD = "Password";
        public const string STAFF_RENEWED_PASSWORD_DATE = "RenewPasswordDate";
        public const string STAFF_DATE_OF_BIRTH = "DateOfBirth";
        public const string STAFF_JOIN_DATE = "JoinDate";
        public const string STAFF_GENDER = "Gender";
        public const string STAFF_RELIGION = "Religion";
        public const string STAFF_POSITION = "Position";
        public const string STAFF_CONTACT = "Contact";
        public const string STAFF_IS_DEFAULT_PASSWORD = "DefaultPassword";
        public const string STAFF_IS_VALID_LOGIN = "IsValidLogin";
        /*****************End Staff Table********************************/


        /******************All About Send Stock Table********************/
        public const string SEND_STOCK_TABLE = "SendStock";
        public const int HQ_SEND_STOCK_PRIMARYKEY_NUMBER = 3;
        public const int SHOP_SEND_STOCK_PRIMARYKEY_NUMBER = 2;
        /******************End Send Stock Table***************************/


        /*****************Select Commands*************************************/
        public const string PRODUCT_TABLE_SELECT_COMMAND = "SELECT * FROM Product ORDER BY ProductID ASC";
        public const string MANUFACTURER_TABLE_SELECT_COMMAND = "SELECT * FROM Manufacturer ORDER BY ManufacturerName ASC;";
        public const string STOCK_TABLE_SELECT_COMMAND = "SELECT * FROM Stock ORDER BY ProductID, BatchID ASC";
        public const string STOCK_DISCOUNT_SELECT_COMMAND = "SELECT * FROM Stock WHERE ExpireDate IS NOT NULL AND DATEDIFF(day, Batchid, ExpireDate) = 1 ORDER BY ProductID, BatchID ASC";
        public const string DISCOUNT_TABLE_SELECT_COMMAND = "SELECT * FROM Discount ORDER BY ProductID ASC";
        public const string REPORT_TABLE_SELECT_COMMAND = "SELECT * FROM Report WHERE ShopID = " + SHOP_SERVER_ID + "ORDER BY ReportDate";
        public const string TOTAL_TRANSACTION_TABLE_SELECT_COMMAND = "SELECT '" + SHOP_SERVER_ID + "' AS ShopID, t.TransactionDate AS ReportDate, SUM(c.CostPrice*c.UnitSold) AS TotalCostPrice, " +
                                                                "SUM(c.SellingPrice*c.Discount) AS TotalSellingPrice, SUM(c.UnitSold) AS SellingQuantity FROM checkoutitem c, transactions t WHERE c.TransactionID = t.TransactionID GROUP BY t.TransactionDate ORDER BY t.TransactionDate";
        public const string TRANSACTIONS_TABLE_SELECT_COMMAND = "SELECT * FROM Transactions ORDER BY TransactionID";
        public const string CHECKOUTITEM_TABLE_SELECT_COMMAND = "SELECT * FROM CheckOutItme ORDER BY TransactionID";
        public const string HQ_REQUEST_TABLE_SELECT_COMMAND = "SELECT * FROM Request WHERE ShopID = " + SHOP_SERVER_ID + "ORDER BY RequestDate, ProductID";
        public const string HQ_APPROVED_REQUEST_TABLE_SELECT_COMMAND = "SELECT * FROM ApprovedRequestStauts WHERE APPROVED = 1 AND ShopID = " + SHOP_SERVER_ID + "ORDER BY RequestDate, ProductID";
        public const string HQ_REJECTED_REQUEST_TABLE_SELECT_COMMAND = "SELECT * FROM ApprovedRequestStauts WHERE APPROVED = 0 AND ShopID = " + SHOP_SERVER_ID + "ORDER BY RequestDate, ProductID";
        public const string SHOP_REQUEST_TABLE_SELECT_COMMAND = "SELECT * FROM Request ORDER BY RequestDate, ProductID";
        /********************End Select Commands******************************/


        /********************Punctuation***************************/
        public const string PUNCTUATION_APOSTROPHE = "'";
        public const string PUNCTUATION_EQUAL = "=";
        public const string PUNCTUATION_COLON = ":";
        public const string SPACE = " ";
        /********************End Punctuation***********************/


        /********************Number***************************/
        public const int ZERO = 0;
        public const int ONE = 1;
        public const int TWO = 2;
        public const double SHELF_LIFE_PERCENTAGE = 0.3;
        public const double STOCK_EXPIREDATE_DISCOUNT = 0.5;
        /********************End Number***********************/

        /********************SynLog***************************/
        public const string LOG_ADD = "Add";
        public const string LOG_UPDATE = "Update";
        public const string LOG_DELETE = "Delete";
        public const string LOG_START_DAILY_SYN = "----------Start Daily Synchron----------";
        public const string LOG_END_DAILY_SYN = "----------End Daily Synchron----------";
        public const string LOG_START_MANUFACTURER = "----------Manufacturer----------";
        public const string LOG_END_MANUFACTURER = "----------End Manufacturer----------";
        public const string LOG_START_PRODUCT = "----------Product----------";
        public const string LOG_END_PRODUCT = "----------End Product----------";
        public const string LOG_START_STOCK = "----------Stock----------";
        public const string LOG_END_STOCK = "----------End Stock----------";
        public const string LOG_START_DISCOUNT = "----------Discount----------";
        public const string LOG_END_DISCOUNT = "----------End Discount----------";
        public const string LOG_START_REPORT = "----------Report----------";
        public const string LOG_END_REPORT = "----------End Report----------";
        /********************End SynLog***********************/

        /********************Daily Stock Update***************************/
        public const string LOG_START_DAILY_UPDATE = "----------Start Daily Update----------";
        public const string LOG_END_DAILY_UPDATE = "----------End Daily Update----------";
        public const string LOG_FROM = " from ";
        public const string LOG_TO = " to ";
        /********************End Daily Stock Update***********************/
    }
}
